package main

import (
	"database/sql"
	"fmt"
	"strings"

	//"fmt"
	_ "github.com/go-sql-driver/mysql" // 使用 _ 匿名加载 MySQL 驱动包
	"log"
)

type Users struct {
	Name     string `db:"name"`
	Email    string `db:"email"`
	Password string `db:"password"`
}

func insertDB(users []*Users) {
	// 存放 (?, ?) 的slice
	valueStrings := make([]string, 0, len(users))
	// 存放values的slice
	valueArgs := make([]interface{}, 0, len(users)*3)
	argsArr := make([]int, 0, len(users))
	// 遍历users准备相关数据
	for key, u := range users {
		// 此处占位符要与插入值的个数对应
		valueStrings = append(valueStrings, "(?, ?, ?)")
		valueArgs = append(valueArgs, u.Name)
		valueArgs = append(valueArgs, u.Email)
		valueArgs = append(valueArgs, u.Password)
		argsArr = append(argsArr, key)
	}

	db, err := sql.Open("mysql",
		"root:root@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 自行拼接要执行的具体语句
	sqlName := fmt.Sprintf("INSERT INTO users (name, email, password) VALUES %s",
		strings.Join(valueStrings, ","))
	// insert
	stmt, err := db.Prepare(sqlName)
	if err != nil {
		log.Fatal(err)
	}
	res, err := stmt.Exec(valueArgs...)
	if err != nil {
		log.Fatal(err)
	}
	lastId, err := res.LastInsertId()
	if err != nil {
		log.Fatal(err)
	}
	rowCnt, err := res.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

}

func main() {
	user := Users{Name: "zsa", Email: "111@qq.com", Password: "111"}
	arr := []*Users{&user, &Users{Name: "zzz", Email: "123ee@qq.com", Password: "123"}}
	insertDB(arr)

	db, err := sql.Open("mysql",
		"root:root@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	var (
		id   int
		name string
	)
	//res, err := db.Exec("select sleep(5)")
	//if err != nil {
	//	log.Fatal(err)
	//}
	//fmt.Println(res)

	rows, err := db.Query("select id, name from users where id in (?,?)", 1, 2)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&id, &name)
		if err != nil {
			log.Fatal(err)
		}
		log.Println(id, name)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	// insert
	/*stmt, err := db.Prepare("INSERT INTO users(name,email,password) VALUES(?,?,?)")
	if err != nil {
		log.Fatal(err)
	}
	res, err := stmt.Exec("Dolly", "tt@123.com", "1111")
	if err != nil {
		log.Fatal(err)
	}
	lastId, err := res.LastInsertId()
	if err != nil {
		log.Fatal(err)
	}
	rowCnt, err := res.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)*/
}
